﻿<%@ WebHandler Language="C#" Class="ContainerShipRecordLookBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;
using System.Collections;

/**
 * Description: 转工看板
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2022.3.25
 */

public class ContainerShipRecordLookBoard : IHttpHandler
{
    private uMESReportOracleDAL dal = new uMESReportOracleDAL();
    private int total = 0;
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["action"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string action = jObject["action"].ToString();
                switch (action)
                {
                    case "getData":
                        r = getData(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getExportExcel":
                        getExportExcel(jObject);
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    DataTable getSearchData(JObject jObject)
    {
        string sql = @"
SELECT t.productname,t.togetherflag,t.specname,t.outperson,t.submitePerson,t.factoryname,t.submitdate,t.plandate,t.estimatedcompletedate,t.status,SUM(t.qty) qty
FROM ( 
     SELECT cci.productname,cci.togetherflag,cci.containername,ws.workflowstepname specname,cci.qty,e.fullname outPerson,
            e2.fullname submitePerson,f.factoryname,to_char(cr.senddate,'yyyy-mm-dd') submitdate,
            cr.needcompletedate plandate,cr.estimatedcompletedate,to_char(csr.recivedate,'yyyy-mm-dd') returndate,cr.ToAcceptDate,csr.CompleteDate,
            CASE WHEN pdr.parentid IS NOT NULL THEN '已拒收'
                 WHEN pdr.parentid IS NULL AND csr.recivedate IS NOT NULL THEN '已转回' 
                 WHEN pdr.parentid IS NULL AND csr.CompleteDate IS NOT NULL AND csr.recivedate IS NULL THEN '已完成'
                 WHEN pdr.parentid IS NULL AND cr.ToAcceptDate IS NOT NULL AND csr.CompleteDate IS NULL AND csr.recivedate IS NULL THEN '已接收'
                 WHEN pdr.parentid IS NULL AND cr.ToAcceptDate IS NULL AND cr.senddate IS NOT NULL  AND csr.recivedate IS NULL THEN '已转出' END status
     FROM containershiprecord cr
     LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
     LEFT JOIN ContainerShipRecive csr ON csr.containershiprecordid = cr.containershiprecordid
     LEFT JOIN workflowstep ws on ws.workflowstepid = cr.workflowstepid
     LEFT JOIN employee e ON e.employeeid = cr.sender
     LEFT JOIN employee e2 ON e2.employeeid = cr.proposerid
     LEFT JOIN factory f ON f.factoryid = cr.tofactoryid
     LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid  AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
     WHERE cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 AND cr.needcompletedate IS NOT NULL 
           AND ((csr.reciver IS NULL) OR (SYSDATE - csr.recivedate) < 1)
";
        //主制分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid ='{0}'", jObject["factoryid"].ToString());
        }

        //承制分厂
        if (jObject["tofactoryid"] != null && jObject["tofactoryid"].ToString() != "")
        {
            sql += string.Format(" AND cr.tofactoryid ='{0}'", jObject["tofactoryid"].ToString());
        }

        //批次号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        // 转出时间
        if (jObject["senddate"] != null && jObject["senddate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["senddate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND TRUNC(cr.sendDate) >= to_date('{0}','yyyy-MM-dd') ", arr[0].ToString());
                sql += string.Format(" AND TRUNC(cr.sendDate) <= to_date('{0}','yyyy-MM-dd') ", arr[1].ToString());
            }
        }

        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "已转出")
            {
                sql += " AND pdr.parentid IS NULL AND cr.ToAcceptDate IS NULL AND cr.senddate IS NOT NULL AND csr.recivedate IS NULL";
            }
            if (jObject["status"].ToString() == "已接收")
            {
                sql += " AND pdr.parentid IS NULL AND cr.ToAcceptDate IS NOT NULL AND csr.CompleteDate IS NULL AND csr.recivedate IS NULL";
            }
            if (jObject["status"].ToString() == "已完成")
            {
                sql += " AND pdr.parentid IS NULL AND csr.CompleteDate IS NOT NULL AND csr.recivedate IS NULL ";
            }
            if (jObject["status"].ToString() == "已转回")
            {
                sql += " AND pdr.parentid IS NULL AND csr.recivedate IS NOT NULL ";
            }
            if (jObject["status"].ToString() == "已拒收")
            {
                sql += " AND pdr.parentid IS NOT NULL ";
            }
        }

        sql += @"
) t
GROUP BY t.productname,t.togetherflag,t.specname,t.outperson,t.submitePerson,t.factoryname,t.submitdate,t.plandate,t.estimatedcompletedate,t.status
ORDER BY t.productname,t.togetherflag,t.specname ";

        DataTable dtResult;
        if (jObject["action"].ToString() == "getData")
        {
            uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
            if (dto.DBTable.Rows.Count == 0)
            {
                return new DataTable();
            }
            dtResult = dto.DBTable;
            total = Convert.ToInt32(dto.RowCount);
        }
        else
        {
            dtResult = dal.GetDataTable(sql);
        }

        dtResult.Columns.Add("returndate", typeof(string));
        dtResult.Columns.Add("ToAcceptDate", typeof(string));
        dtResult.Columns.Add("CompleteDate", typeof(string));
        dtResult.Columns.Add("delayDays", typeof(string));

        DataTable dtFilter = dtResult.DefaultView.ToTable(true, "togetherflag", "specname");

        StringBuilder sbFilter = new StringBuilder();
        sbFilter.Append("(");
        foreach (DataRow dr in dtFilter.Rows)
        {
            sbFilter.AppendFormat("(cci.togetherflag = '{0}' AND ws.workflowstepname = '{1}') OR ", dr["togetherflag"], dr["specname"]);
        }
        sbFilter.Remove(sbFilter.ToString().Length - 3, 3);
        sbFilter.Append(")");

        StringBuilder sb = new StringBuilder();
        sb.AppendLine("SELECT cci.productname,cci.togetherflag,cci.containername,ws.workflowstepname specname,cci.qty,");
        sb.AppendLine("       f.factoryname,to_char(cr.senddate,'yyyy-mm-dd') submitdate,");
        sb.AppendLine("       cr.needcompletedate plandate,cr.estimatedcompletedate,to_char(csr.recivedate,'yyyy-mm-dd') returndate,");
        sb.AppendLine("       to_char(cr.ToAcceptDate,'yyyy-mm-dd') ToAcceptDate,to_char(csr.CompleteDate,'yyyy-mm-dd') CompleteDate,");
        sb.AppendLine("       CASE WHEN pdr.parentid IS NOT NULL THEN 0 ");
        sb.AppendLine("            WHEN pdr.parentid IS NULL AND csr.CompleteDate IS NULL ");
        sb.AppendLine("                 THEN TRUNC((SYSDATE - to_date(to_char(cr.needcompletedate,'yyyy/mm/dd') || ' 23:59:59','yyyy/mm/dd hh24:mi:ss') ),2)");
        sb.AppendLine("            ELSE TRUNC((csr.CompleteDate - to_date(to_char(cr.needcompletedate,'yyyy/mm/dd') || ' 23:59:59','yyyy/mm/dd hh24:mi:ss') ),2) END delayDays,");
        sb.AppendLine("       CASE WHEN pdr.parentid IS NOT NULL THEN '已拒收'");
        sb.AppendLine("            WHEN pdr.parentid IS NULL AND csr.recivedate IS NOT NULL THEN '已转回' ");
        sb.AppendLine("            WHEN pdr.parentid IS NULL AND csr.CompleteDate IS NOT NULL AND csr.recivedate IS NULL THEN '已完成'");
        sb.AppendLine("            WHEN pdr.parentid IS NULL AND cr.ToAcceptDate IS NOT NULL AND csr.CompleteDate IS NULL AND csr.recivedate IS NULL THEN '已接收'");
        sb.AppendLine("            WHEN pdr.parentid IS NULL AND cr.ToAcceptDate IS NULL AND cr.senddate IS NOT NULL  AND csr.recivedate IS NULL THEN '已转出' END status");
        sb.AppendLine("FROM containershiprecord cr");
        sb.AppendLine("LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid");
        sb.AppendLine("LEFT JOIN ContainerShipRecive csr ON csr.containershiprecordid = cr.containershiprecordid");
        sb.AppendLine("LEFT JOIN workflowstep ws on ws.workflowstepid = cr.workflowstepid");
        sb.AppendLine("LEFT JOIN factory f ON f.factoryid = cr.tofactoryid");
        sb.AppendLine("LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)");
        sb.AppendLine("WHERE cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 AND cr.needcompletedate IS NOT NULL ");
        sb.AppendLine("      AND ((csr.reciver IS NULL) OR (SYSDATE - csr.recivedate) < 1)");
        if (jObject["factoryid"] != null)
        {
            sb.AppendFormat(" AND cci.originalfactoryid ='{0}' ", jObject["factoryid"].ToString());
        }
        sb.Append("AND" + sbFilter.ToString());

        DataTable dtDetail = dal.GetDataTable(sb.ToString());

        foreach (DataRow dr in dtResult.Rows)
        {
            string filter = string.Format("togetherflag = '{0}' and specname = '{1}' and status = '{2}'", dr["togetherflag"], dr["specname"], dr["status"]);
            DataRow[] datarowList = dtDetail.Select(filter);

            if (datarowList.Length > 0)
            {
                dr["returndate"] = datarowList[0]["returndate"];
                dr["ToAcceptDate"] = datarowList[0]["ToAcceptDate"];
                dr["CompleteDate"] = datarowList[0]["CompleteDate"];
                dr["delayDays"] = datarowList[0]["delayDays"];
            }
            else
            {
                dr["returndate"] = "";
                dr["ToAcceptDate"] = "";
                dr["CompleteDate"] = "";
                dr["delayDays"] = "";
            }
        }

        foreach (DataRow dr in dtResult.Rows)
        {
            double dalayDays = Convert.ToDouble(dr["delayDays"].ToString());
            if (dalayDays < 0)
            {
                dr["delayDays"] = 0;
            }
        }

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        return dtResult;
    }

    Result getData(JObject jObject)
    {
        Result r = new Result();
        DataTable dt = getSearchData(jObject);
        r.data = dt;
        r.total = Convert.ToInt32(total);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void getExportExcel(JObject jObject)
    {
        DataTable dtResult = getSearchData(jObject);
        dtResult.Columns.Remove("submiteperson");

        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["togetherflag"].Caption = "批次号";
        dtResult.Columns["specname"].Caption = "转出工序";

        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["outperson"].Caption = "转出人";
        dtResult.Columns["factoryname"].Caption = "承制分厂";
        dtResult.Columns["submitdate"].Caption = "转出时间";
        dtResult.Columns["plandate"].Caption = "要求完成时间";
        dtResult.Columns["estimatedcompletedate"].Caption = "预计完成时间";
        dtResult.Columns["toacceptdate"].Caption = "接收时间";
        dtResult.Columns["completedate"].Caption = "完成日期";
        dtResult.Columns["returndate"].Caption = "转入日期";
        dtResult.Columns["delaydays"].Caption = "超期天数";
        dtResult.Columns["status"].Caption = "状态";
        ExportHelper.ExportExcel(dtResult, jObject["factoryname"].ToString() + "转工看板");
    }
}